import pandas as pd
import matplotlib.pyplot as plt

plt.rcParams['font.sans-serif'] = ['SimHei']

score_data = {
    '学号': ['16010203', '16010210', '16010205', '16010213', '16010215', '16010208', '16010209', '16010204',
            '16010211', '16010212', '16010206', '16010214', '16010207'],
    'C#': [78, 87, 84, 86, 67, 76, 56, 89, 81, 73, 65, 90, 91],
    '线代': [88, 58, 65, 72, 76, 43, 68, '缺考', 81, 77, 80, 73, 64],
    'python': [96, 83, 82, 67, 85, 69, 92, 86, 75, 69, 84, 91, 86]
}
score = pd.DataFrame(score_data)

info_data = {
    '姓名': ['张三', '李四', '王五', '赵六', '郑七', '钱八', '张千', '赵六', '李矛', '张白', '白九', '冀二', '余一'],
    '学号': ['16010203', '16010204', '16010205', '16010206', '16010207', '16010208', '16010209', '16010210',
            '16010211', '16010212', '16010213', '16010214', '16010215'],
    '手机号码': ['16699995521', '16699995522', '16699995523', '16699995524', '16699995525', '16699995526',
               '16699995527', '16699995528', '16699995529', '16699995510', '16699995511', '16699995512',
               '16699995513']
}
info = pd.DataFrame(info_data)
print(info)

score = pd.merge(score, info[['姓名', '学号']], on='学号', how='left')


def zs(s):
    totle = 0
    for i in ['C#', '线代', 'python']:
        if pd.notnull(s[i]) and s[i]!= '缺考':
            totle += float(s[i])
    return totle


score['总分'] = score.apply(zs, axis=1)


def dj(z):
    if z >= 90:
        return '优'
    elif z >= 80:
        return '良'
    elif z >= 70:
        return '中'
    elif z >= 60:
        return '及格'
    else:
        return '不及格'


score['等级'] = score['总分'].apply(dj)

print(score)
print(info)

l = ['C#', '线代', 'python']
for i in l:
    zjz = score[score[i]!= '缺考'][i].astype(float)
    pz = zjz.mean()
    fc = zjz.std()
    print(f'{i}的平均成绩: {pz}')
    print(f'{i}的标准差: {fc}')

zzz=score['总分'].mean()
plt.scatter(score['学号'], score['总分'])
plt.axhline(y=zzz, color='r', linestyle='--', label='总分均值')
plt.xlabel('学号')
plt.ylabel('总分')
plt.title('总分成绩分布图')
plt.legend()
plt.show()
# import pandas as pd
# import numpy as np
#
# data = {
#     '学号': ['2308024241', '2308024244', '2308024251', '2308024249', '2308024219', '2308024201', '2308024347',
#              '2308024307', '2308024326', '2308024320', '2308024342', '2308024310', '2308024435', '2308024432',
#              '2308024446', '2308024421', '2308024433', '2308024428', '2308024402', '2308024422', '2308024201'],
#     '班级': ['23080242', '23080242', '23080242', '23080242', '23080242', '23080242', '23080243', '23080243',
#             '23080243', '23080243', '23080243', '23080243', '23080244', '23080244', '23080244', '23080244',
#             '23080244', '23080244', '23080244', '23080244', '23080242'],
#     '姓名': ['成龙', '周怡', '张波', '朱浩', '封印', '迟培', '李华', '陈田', '余皓', '李嘉', '李上初', '郭窦',
#             '姜毅涛', '赵宇', '周路', '林建祥', '李大强', '李侧通', '王慧', '李晓亮', '迟培'],
#     '性别': ['男', '女', '男', '男', '女', '男', '女', '男', '男', '女', '男', '女', '男', '男', '女', '男',
#              '男', '男', '女', '男', '男'],
#     '英语': [76, 66, 85, 65, 73, 60, 67, 76, 66, 62, 76, 79, 77, 74, 76, 72, 79, 64, 73, 85, 60],
#     '体育': [78, 91, 81, 50, 88, 50, 61, 79, 67, '作弊', 90, 67, 71, 74, 80, 72, 76, 96, 74, 60, 50],
#     '军训': [77, 75, 75, 80, 92, 89, 84, 86, 85, 90, 84, 84, '缺考', 88, 77, 81, 77, 91, 93, 85, 89],
#     '数分': [40, 47, 45, 72, 61, 71, 61, 69, 65, 60, 60, 64, 61, 68, 61, 63, 78, 69, 70, 72, 71],
#     '高代': [23, 47, 45, 62, 47, 76, 65, 40, 61, 67, 66, 64, 73, 70, 74, 90, 70, 60, 71, 72, 76],
#     '解几': [60, 44, 60, 71, 46, 71, 78, 69, 71, 77, 60, 79, 76, 71, 80, 75, 70, 77, 75, 83, 71]
# }
#
# b = pd.DataFrame(data)
# b=b.drop_duplicates()
# b=b.fillna(0)
# b['体育'] = np.where(b['体育'] == '作弊', 0, b['体育'])
# b['军训'] = np.where(b['军训'] == '缺考', 0, b['军训'])
# xml=b.select_dtypes(include=['object']).columns
# for i in xml:
#     b[i]=b[i].astype(str).str.strip()
# l=['英语','体育','军训','数分','高代','解几']
# for i in l:
#     b[i]=b[i].astype(float)
# def zf(b):
#     return b['英语']+b['体育']+b['军训']+b['数分']+b['高代']+b['解几']
# b['总分']=b.apply(zf,axis=1)
# mi=b['总分'].min()
# ma=b['总分'].max()
# bins=[mi-1,400,450,ma+1]
# labals=['一般','较好','优秀']
# b['等级']=pd.cut(b['总分'],bins=bins,labels=labals)
# print(b)
# def biaozhun(s):
#     v=s.mean()
#     st=s.std()
#     return (s-v)/st
# l=['英语','体育','军训','数分','高代','解几']
# for i in l:
#     b[i+'标准差']=biaozhun(b[i])
# s=[i for i in b.columns if i.endswith('标准差')]
# def biaozhunsong(b):
#
#     return b[s].sum()
# b['标准差总分']=b.apply(biaozhunsong,axis=1)
#
# bi=[b['标准差总分'].min()-1,b['标准差总分'].quantile(0.33),b['标准差总分'].quantile(0.66),b['标准差总分'].max()+1]
# b['标准等级']=pd.cut(b['标准差总分'],bins=bi,labels=labals)
# print(b)

